ディメンショナルモデリングに入門しよう!Snowflakeとdbt Cloudで「Building a Kimball dimensional model with dbt」をやってみた
さがらです。
ここ2年ほどの間にdbtが日本でも急速に拡大し、様々な情報が日本語の記事でも見かけられるようになってきました。
dbtを採用してある程度活用を進めていくと、「より効率よくガバナンスを持ってデータを管理するにはどうすればいいんだろうか」といったデータの管理方法に悩む場面が出てくると思います。
そんなときに色々調べていくと、データを効率よく管理する手法として「データモデリング」が必要だとわかり、ディメンショナルモデリングやData Vaultなどの手法に行き着くのではないでしょうか。
そしてこれらのデータモデリングの手法の内、ディメンショナルモデリングについてdbtを用いて実践された記事がありまして、それが「Building a Kimball dimensional model with dbt」です!
この「Building a Kimball dimensional model with dbt」ですが、dbt CoreとDuckDB/PostgreSQLという組み合わせで内容が書かれています。
しかし、実際に使用するデータはdbt seed
コマンドでリポジトリ上のCSVから生成しているだけですし、dbt Coreでのみ使えるPythonライブラリを使っているわけでもないので、dbt Cloudと他のDWHにも展開可能な内容です。
そこで、Snowflakeとdbt Cloudを使って「Building a Kimball dimensional model with dbt」をやってみたので、本記事でその内容をまとめてみます。
前置き:ディメンショナルモデリングの知識について
「Building a Kimball dimensional model with dbt」はディメンショナルモデリングを実践する際のプロセスと、dbtでどのように実装するかに重きを置いており、ディメンショナルモデリング自体の知識については深く書かれておりません。
そのため、ディメンショナルモデリングの知識をどのように習得すればよいか、参考になる記事や本について一例を記載しておきます。
ぺいさんの各種スライドやZennの記事
私も色んな情報を見ましたが、日本語で一番わかりやすくまとまっていると感じたのはぺいさん(@pei0804)のスライドやZennの記事です。
まずはこれらのスライドや記事を見ることで、ディメンショナルモデリングはどういったものか、どのように役立つのか、どのようにモデリングを進めていけばよいか、がわかると思います。
- スライド:ディメンショナル モデリング入門 / introduction-to-dimensional-modeling
- Zenn:ディメンショナル・モデリング
- Zenn:スタースキーマ(基礎)
- Zenn:複数スタースキーマ
- Zenn:コンフォームド・ディメンション(Conformed Dimensions)
- Zenn:スノーフレークスキーマ
Star Schema: The Complete Reference
こちらは書籍となるのですが、「Star Schema: The Complete Reference」が私がXでフォローしている方々から良い評判を聞きます。(実は私も読めていないので、今後読んでいかなければと思っています…)
※ディメンショナルモデリングについては他にも色々な本があるので、あくまで参考までに。
検証環境
- Snowflake:Ver8.2.0
- dbtの開発・実行環境:dbt Cloud
- 使用するdbtのバージョン:1.7 ※Environmentsで指定
1.リポジトリ・データベース・dbt projectのセットアップ
実際の内容に入る前に、リポジトリ・Snowflake・dbt Cloud、それぞれ準備をする必要がありますのでその内容について記載します。
※今回はあくまで検証のため、簡単にセットアップを行います。実際に業務で用いる環境としては、設計したレイヤー分けに応じたデータベースやスキーマの準備、対象のデータベース・スキーマにのみ権限を持つSnowflakeのロールの準備、などが必要となりますのでご注意ください。
リポジトリのfork
「Building a Kimball dimensional model with dbt」の内容はGitHub上にPublicのリポジトリとして公開されているため、この内容をforkして使えるようにしておきます。
Snowflakeでの検証用データベース・ウェアハウスの準備
dbtで処理を行う際に必要となるデータベースとウェアハウスをSnowflakeで作成します。以下のようなクエリを実行すればOKです。
use role sysadmin; create database <任意のデータベース名>; create or replace warehouse <任意のウェアハウス名> with warehouse_size = 'xsmall' warehouse_type = 'standard' auto_suspend = 60 auto_resume = true min_cluster_count = 1 max_cluster_count = 1 scaling_policy = 'standard' initially_suspended = true; -- 任意:dbtをsysadmin以外のロールで実行する場合には、そのロールに対して必要な権限をgrantしてください。以下ではわかりやすくownershipをgrantしています。 grant ownership on database <任意のデータベース名> to role <任意のロール名>; grant ownership on warehouse <任意のウェアハウス名> to role <任意のロール名>;
dbt Cloudでのdbt projectのセットアップ
前述の工程で準備したリポジトリとSnowflakeのデータベースを用いて、dbt Cloudでdbt projectのセットアップを行います。
こちらの手順については、下記のブログも参考になると思います。
1つ注意点としては、対象のリポジトリのサブディレクトリadventureworks
にdbtの各種ファイルが保存されているため、以下のセットアップ画面ではProject subdirectory
にadventureworks/
と入力してください。
dbt seedを実行しデータを生成
これまでの工程を終えたら、あとはdbt Cloudで作成したdbt projectのIDEにおいてdbt seed
コマンドを実行すれば、モデリング前のテーブルを生成してくれます!
※各seedのyamlファイルでschema: person
のようなconfigが書かれており、generate_schema_name
マクロもoverride_default_schema_name.sql
で書き換えているため、configの値に応じたスキーマが自動で作られ、そのスキーマにテーブルが作られます。ただし、このリポジトリのoverride_default_schema_name.sql
は実際の本番運用には適さないマクロとなっているためご注意ください。本番運用に適したgenerate_schema_name
マクロの書き換えについては、こちらのブログも参考にしてください。
モデリング前のデータの内容を確認する
先程dbt seed
で作られたテーブルですが、以下のようなリレーションとなっています。(ブログより引用)
実際のモデリング前には、「どんなデータを持つテーブルがあるか」「各テーブルの主キー・外部キーはなにか」「リレーションはどうなっているか」などを確認すると良いでしょう。
また、実際にクエリを発行することでどんなデータがあるか見ることも出来ます。
2.ビジネスプロセスの選択
ということで元データが揃ったので「モデリングの設計と実装するぞー!」となりがちですが、これは間違いです。
まず、今回モデリング対象のビジネスについての理解を深める必要があります。基本的には、対象のデータを実際に業務に活用しているビジネスユーザーにヒアリングをすることになります。
以下は一例ですが、このようなことをビジネスユーザーにヒアリングしておくと良いでしょう。
- どういったことを実現したいのか
- なぜそのデータが必要なのか
- 現在はどのように対応しているのか(データの加工をExcelなどで頑張っているケースも多いので、そのExcelについて詳細にヒアリングしても良い)
- etc
今回はブログに沿って進めるため、AdventureWorks社のCEOから以下の情報を得たとします。
- AdventureWorks社は自転車を製造し、消費者 (B2C) および企業 (B2B) に販売しています。自転車は世界中の顧客に出荷されます。CEOとして、2011年までに当社が生み出した収益の内訳を知りたいと思っています。
- 収益の内訳については、以下の分類で知りたいです。
- Product category and subcategory
- Customer
- Order status
- Shipping country, state, and city
この情報から、収益とその内訳を知るための、Salesプロセスに関するモデリングを行えば良いということがわかりました。
3.ファクトテーブルとディメンションテーブルの特定
前述のヒアリングした内容に基づいて、ファクトテーブルとディメンションテーブルを特定していきます。
ファクトテーブルの特定
まずファクトテーブルですが、ビジネスプロセスにおける数値や指標を表すテーブルです。具体的には、「アイテムの販売数」「ウェブサイトのクリック数」などの具体的な値を持つテーブルとなります。
今回は「収益とその内訳を知るための、Salesプロセスに関するモデリング」ですので、ファクトは「収益」となります。
その収益に関連するテーブルはどのようなものがあるか調査すると、sales.salesorderheader
テーブルとsales.salesorderdetail
テーブルがあることがわかりました。
sales.salesorderheader
テーブル:- 注文に使用されたクレジット カード、配送先住所、および顧客に関する情報が含まれています。このテーブルの各レコードは、1 つ以上の注文の詳細を含む注文ヘッダーを表します。
sales.salesorderdetail
テーブル:- 注文された製品、注文数量、単価に関する情報が含まれており、収益の計算に使用できます。このテーブルの各レコードは、単一の注文の詳細を表します。
そして、これらのテーブルはリレーションを見ると下図のような関係となっています。
これらのテーブルをJOINすることで、各レコードが注文の詳細を表し、集計することで収益を知ることができるファクトテーブルとすることができます。
ここでは、テーブル名はfct_sales
と定義します。
ディメンションテーブルの特定
続いてディメンションテーブルですが、ビジネスプロセスにおけるコンテキスト情報や説明情報を表すテーブルです。具体的には、「顧客の情報」「Webサイトでクリックされたときのクリック位置の情報」「注文された商品の分類や商品名などの詳細情報」などのデータを持つテーブルとなります。
改めて振り返ると、事前にヒアリングした情報では「以下の情報で収益の内訳を知りたい」とのことでした。
- Product category and subcategory
- Customer
- Order status
- Shipping country, state, and city
このヒアリングした情報を元に、関連するテーブルを洗い出すと以下のようなテーブルが出てきます。
person.address
person.countryregion
production.product
production.productcategory
sales.customer
sales.store
- etc
上述のテーブルがディメンションテーブルとなるため、下図のようにこれらのテーブルをそのままディメンションテーブルとすることもできますが、正規化されているためクエリ実行の際に都度複数のJOINが発生することになってしまいます。
※余談ですが、このようなディメンションテーブルからディメンションテーブルが連なる構造をスノーフレークスキーマと呼びます。
そこで今回は、このJOINを減らすために、下図のようにディメンションテーブル同士の結合を事前に行い非正規化をして、各ディメンションテーブルから直接ファクトテーブルと結合するようにモデリングをします。
※余談ですが、このようなファクトテーブルを中心に非正規化したディメンションテーブルを1つずつ構成していく構造をスタースキーマと呼びます。
ここでは、他のテーブルについても同様の非正規化を行い、以下のディメンションテーブルを定義します。
dim_product
:product
、productsubcategory
、productcategory
の3つのテーブルをJOINしたディメンションテーブルdim_address
:address
、stateprovince
、countryregion
の3つのテーブルをJOINしたディメンションテーブルdim_customer
:customer
、person
、store
の3つのテーブルをJOINしたディメンションテーブルdim_credit_card
:creditcard
テーブルを元にしたディメンションテーブルdim_order_status
:salesorderheader
テーブルからorder_status
列だけを抽出して、重複レコードの削除を行ったディメンションテーブルdim_date
:dbt_date
packageを用いて、生成された日付属性を含むディメンションテーブル
4.ディメンションテーブルの実装
前述の内容で作成すべきファクトテーブルとディメンションテーブルの定義を終えたので、実際にそれぞれのテーブルを実装していきます。
まずはディメンションテーブルについてです。リポジトリ上にすでに各ファイルは出来上がっていますが、ブログに沿って、dbtでどのような記述を行いディメンショナルテーブルを実装していくのか、dim_product
テーブルの実装手順を例に、書いていきます。
ステップ1.Modelファイルの作成
まず、Modelファイルを作成します。このブログでは、「adventureworks/models/marts/
フォルダの中で、1つのModelに対して1つのyamlファイル」を作成する方法を取っています。
具体的に、dim_product
というディメンションテーブルを作成する場合、以下の2つのファイルを作成します。
dim_product.sql
:CTASとして実行される、SELECT文のSQLを記載するdim_product.yml
:dim_product
テーブルに対するテストやDescriptionの内容を記載する
ステップ2.SQLの記述
ステップ2では、実際にdim_product.sql
においてどのようにコードを書いていくか説明していきます。
実際のdim_product.sql
のコードが下記になります。
with stg_product as ( select * from {{ ref('product') }} ), stg_product_subcategory as ( select * from {{ ref('productsubcategory') }} ), stg_product_category as ( select * from {{ ref('productcategory') }} ) select {{ dbt_utils.generate_surrogate_key(['stg_product.productid']) }} as product_key, stg_product.productid, stg_product.name as product_name, stg_product.productnumber, stg_product.color, stg_product.class, stg_product_subcategory.name as product_subcategory_name, stg_product_category.name as product_category_name from stg_product left join stg_product_subcategory on stg_product.productsubcategoryid = stg_product_subcategory.productsubcategoryid left join stg_product_category on stg_product_subcategory.productcategoryid = stg_product_category.productcategoryid
このコードにおけるポイントは以下になります。
- SQLの書き方として、Common Table Expressions (CTE)を採用している
- 上流に位置するテーブルはref関数を使用して参照する
- ファクトテーブルと結合するためのキーは、
dbt_utils
パッケージのgenerate_surrogate_key
マクロを用いて代理キーを生成する- 代理キーを生成することで、ユーザーがカラム名を見て直感的にファクトテーブルとディメンションテーブルのJOINを行うことができる
dbt_utils
パッケージのgenerate_surrogate_key
マクロを使うことで、各テーブルで一意となるカラムの値をハッシュ化することができる
- 最終的にselectするカラムは、後でファクトテーブルとJOINして収益の内訳を知るために役立つカラムのみにする
ステップ3.yamlの記述
続いてステップ3ですが、実際にdim_product.yml
でどのようにテストやDescriptionを記述しているか説明していきます。
実際のdim_product.yml
のコードが下記になります。product_key
とproductid
という代理キー・ナチュラルキーのカラムについてはnot_null
とunique
のテストを入れています。
version: 2 models: - name: dim_product columns: - name: product_key description: The surrogate key of the product tests: - not_null - unique - name: productid description: The natural key of the product tests: - not_null - unique - name: product_name description: The product name tests: - not_null
5.ファクトテーブルの実装
次はファクトテーブルについてです。ブログに沿って、dbtでどのような記述を行いファクトテーブルを実装していくのか、fct_sales
テーブルの実装手順を例に、書いていきます。
ステップ1.Modelファイルの作成
まず、Modelファイルを作成します。ディメンションテーブルと同様に「adventureworks/models/marts/
フォルダの中で、1つのModelに対して1つのyamlファイル」を作成していきます。
具体的に、fct_sales
というディメンションテーブルを作成する場合、以下の2つのファイルを作成します。
fct_sales.sql
:CTASとして実行される、SELECT文のSQLを記載するfct_sales.yml
:fct_sales
テーブルに対するテストやDescriptionの内容を記載する
ステップ2.SQLの記述
ステップ2では、実際にfct_sales.sql
においてどのようにコードを書いていくか説明していきます。
実際のfct_sales.sql
のコードが下記になります。
with stg_salesorderheader as ( select salesorderid, customerid, creditcardid, shiptoaddressid, status as order_status, cast(orderdate as date) as orderdate from {{ ref('salesorderheader') }} ), stg_salesorderdetail as ( select salesorderid, salesorderdetailid, productid, orderqty, unitprice, unitprice * orderqty as revenue from {{ ref('salesorderdetail') }} ) select {{ dbt_utils.generate_surrogate_key(['stg_salesorderdetail.salesorderid', 'salesorderdetailid']) }} as sales_key, {{ dbt_utils.generate_surrogate_key(['productid']) }} as product_key, {{ dbt_utils.generate_surrogate_key(['customerid']) }} as customer_key, {{ dbt_utils.generate_surrogate_key(['creditcardid']) }} as creditcard_key, {{ dbt_utils.generate_surrogate_key(['shiptoaddressid']) }} as ship_address_key, {{ dbt_utils.generate_surrogate_key(['order_status']) }} as order_status_key, {{ dbt_utils.generate_surrogate_key(['orderdate']) }} as order_date_key, stg_salesorderdetail.salesorderid, stg_salesorderdetail.salesorderdetailid, stg_salesorderdetail.unitprice, stg_salesorderdetail.orderqty, stg_salesorderdetail.revenue from stg_salesorderdetail inner join stg_salesorderheader on stg_salesorderdetail.salesorderid = stg_salesorderheader.salesorderid
このコードにおけるポイントは以下になります。
- SQLの書き方として、Common Table Expressions (CTE)を採用している
- 上流に位置するテーブルはref関数を使用して参照する
- ファクトテーブルの1行の粒度は
SalesOrderDetail
テーブルの1つのレコードと同じ粒度のため、salesorderheader
テーブルとsalesorderdetail
テーブルは内部結合でJOINする - ファクトテーブルの各行を一意に識別するための代理キーは、
dbt_utils
パッケージのgenerate_surrogate_key
マクロを用いて代理キーを生成するgenerate_surrogate_key(['stg_salesorderdetail.salesorderid', 'salesorderdetailid'])
とすることで、引数に2つのカラムを指定した上でハッシュ化できる
- ディメンションテーブルと結合するための外部代理キーは、
dbt_utils
パッケージのgenerate_surrogate_key
マクロを用いて代理キーを生成する- 前工程で定義したディメンションテーブルの代理キーと、カラム名を合わせるようにする
- 今回のモデリングの目的でもある「収益」を計算するために、必要な
orderqty
列とunitprice
列を選択した上で、収益額を表すrevenue
列を追加
ステップ3.yamlの記述
続いてステップ3ですが、実際にfct_sales.yml
でどのようにテストやDescriptionを記述しているか説明していきます。
実際のfct_sales.yml
のコードが下記になります。代理キーであるsales_key
にはnot_null
とunique
のテストを、外部代理キーの各カラムと実際の値を持つ各カラムについてはnot_null
のテストを入れています。
version: 2 models: - name: fct_sales columns: - name: sales_key description: The surrogate key of the fct sales tests: - not_null - unique - name: salesorderid description: The natural key of the saleorderheader tests: - not_null - name: salesorderdetailid description: The natural key of the salesorderdetail tests: - not_null - name: product_key description: The foreign key of the product tests: - not_null - name: customer_key description: The foreign key of the customer tests: - not_null - name: ship_address_key description: The foreign key of the shipping address tests: - not_null - name: creditcard_key description: The foreign key of the creditcard. If no creditcard exists, it was assumed that purchase was made in cash. test: - not_null - name: order_date_key description: The foreign key of the order date tests: - not_null - name: order_status_key description: The foreign key of the order status tests: - not_null - name: unitprice description: The unit price of the product tests: - not_null - name: orderqty description: The quantity of the product tests: - not_null - name: revenue description: The revenue obtained by multiplying unitprice and orderqty
6.開発したModelのビルド
これまでのプロセスでディメンションテーブルとファクトテーブルのModelの開発を終えたので、ビルドをして実際にSnowflakeに反映させてみます。
また、今回のリポジトリではdbt_project.yml
で下記のように記述しているため、全てのModelがテーブルとして生成されます。実際にはファクトテーブルは行数が多くなる傾向にあるため、必要に応じてincremental
のModelもご検討ください。
models: adventureworks: marts: +materialized: table +schema: marts
ということで、dbt build
コマンドを実行してみます!
下図のように、MARTSスキーマに各ディメンション・ファクトのテーブルが出来ればOKです!
7.実装後の参照方法
ディメンショナルモデルを行った後の参照方法ですが、ここではブログに沿って、2パターンに分けてご紹介します。
使用するツールが動的なJOINに対応している場合
例えば、LookerのExplore、dbt Semantic Layer、SnowflakeにRELY制約を設けたView、Tableauでのリレーションシップや結合などは、ファクトテーブルとすべてのディメンションテーブルのJOINの定義だけをしておけば、SELECTされたカラムに応じて動的に必要なJOINだけを行うことが可能です。
実際に参照するエンドユーザーがJOINに詳しくなくてもディメンショナルモデリングの効果を自然に得られるので、これらのBIツールやDWHを使っている方にはぜひ試していただきたいです!
使用するツールが動的なJOINに対応しておらず、ユーザーもJOINに詳しくない場合
ただ、エンドユーザーがJOINに詳しくなく、参照時のツールが動的なJOINに参照していない場合も多いと思います。
そんなときは、One Big Table(略してOBT。ワイドテーブル、大福帳とも呼ばれる)を1つdbtで実装してあげると良いと思います!
実際このブログでも1つOBTを実装しており、obt_sales.sql
で定義されています。クエリの内容としては、シンプルにすべてのファクトとディメンションをJOINしているだけです。
with f_sales as ( select * from {{ ref('fct_sales') }} ), d_customer as ( select * from {{ ref('dim_customer') }} ), d_credit_card as ( select * from {{ ref('dim_credit_card') }} ), d_address as ( select * from {{ ref('dim_address') }} ), d_order_status as ( select * from {{ ref('dim_order_status') }} ), d_product as ( select * from {{ ref('dim_product') }} ), d_date as ( select * from {{ ref('dim_date') }} ) select {{ dbt_utils.star(from=ref('fct_sales'), relation_alias='f_sales', except=[ "product_key", "customer_key", "creditcard_key", "ship_address_key", "order_status_key", "order_date_key" ]) }}, {{ dbt_utils.star(from=ref('dim_product'), relation_alias='d_product', except=["product_key"]) }}, {{ dbt_utils.star(from=ref('dim_customer'), relation_alias='d_customer', except=["customer_key"]) }}, {{ dbt_utils.star(from=ref('dim_credit_card'), relation_alias='d_credit_card', except=["creditcard_key"]) }}, {{ dbt_utils.star(from=ref('dim_address'), relation_alias='d_address', except=["address_key"]) }}, {{ dbt_utils.star(from=ref('dim_order_status'), relation_alias='d_order_status', except=["order_status_key"]) }}, {{ dbt_utils.star(from=ref('dim_date'), relation_alias='d_date', except=["date_key"]) }} from f_sales left join d_product on f_sales.product_key = d_product.product_key left join d_customer on f_sales.customer_key = d_customer.customer_key left join d_credit_card on f_sales.creditcard_key = d_credit_card.creditcard_key left join d_address on f_sales.ship_address_key = d_address.address_key left join d_order_status on f_sales.order_status_key = d_order_status.order_status_key left join d_date on f_sales.order_date_key = d_date.date_key
このようなOBTが1つあると、JOINに詳しくないユーザーも参照できるので良いと思います。ただ、OBTを作りすぎるとデータカオスとなるリスクもあるため、「1つのスタースキーマに対して1つのOBT」くらいから始めるのが良いと思います。
OBTも含めた、dbtで閲覧できるリネージは下図のようになりました。
最後に
Snowflakeとdbt Cloudで「Building a Kimball dimensional model with dbt」をやってみました。
リポジトリもあってforkすればすぐに動かせますし、ディメンショナルモデリングの入門としてとても良いチュートリアルだと感じました。ぜひお試しください!